-- @owner: wang_zhengyuan
-- @date: 2022-02-18
-- @testpoint: join表使用视图，视图中有union all及索引; 多次使用connect by

--step1:join表使用视图，视图中有union all及索引; 多次使用connect by;expect:成功
with w_1 as (
	select count
		( t1.id + t2.id ) over ( partition by t1.id - t2.id ) id
	from
		t_ustore_dql_sqlstrength_001 t1
		join t_ustore_dql_sqlstrength_001 t2
		    on t1.id > t2.id
	union all
	select
		t1.id + t2.id id
	from
		t_ustore_dql_sqlstrength_001 t1
		join t_ustore_dql_sqlstrength_001 t2
		    on t1.id = t2.id
	),
	w_2 as (
	select distinct
		t1.id + t5.id id,
		t1.id + t3.c_int c_int
	from
		w_1 t1
		left join t_ustore_dql_sqlstrength_001 t2
		    on t2.id > t1.id,
		    t_ustore_dql_sqlstrength_001 t3
		inner join t_ustore_dql_sqlstrength_001 t4
		    on t3.id = t4.id
		join w_1 t5
		    on t5.id = t3.id
	)
select count
	( * )
from
	( ( select /*+ parallel(4)*/ id, c_int c_int
	    from w_2
	    union all
	    select distinct id, id + 10 from w_1 ) );